﻿using Dapper;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData
{
    public static class DapperExtension
    {
        private static Dictionary<Type, (string TableName, List<string> AllColumns, List<string> WriteableColumns, List<string> PrimaryKeys, bool IsAutoIncrement)> CachedObjectProperties = new Dictionary<Type, (string, List<string>, List<string>, List<string>, bool)>();

        #region 生成 SQL 方法
        /// <summary>
        /// 获取查询所有的 SQL 语句。
        /// </summary>
        /// <param name="objType"></param>
        /// <param name="tableName">表名。</param>
        /// <returns></returns>
        internal static string GetSelectSql(this Type objType, string tableName = null)
        {
            var table = GetEntityColumns(objType);

            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = table.TableName;
            }

            return string.Format("SELECT `{1}` FROM `{0}`", tableName, string.Join("`, `", table.AllColumns));
        }

        /// <summary>
        /// 获取通过主键查询的 SQL 语句。
        /// </summary>
        /// <param name="objType"></param>
        /// <param name="tableName">表名。</param>
        /// <returns></returns>
        internal static string GetSelectSqlByPrimaryKeys(this Type objType, string tableName = null)
        {
            var table = GetEntityColumns(objType);

            if (table.PrimaryKeys.Count == 0)
            {
                throw new InvalidOperationException("当前 Entity 没有定义主键。");
            }

            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = table.TableName;
            }

            return string.Format("SELECT `{1}` FROM `{0}` WHERE {2}", tableName, string.Join("`, `", table.AllColumns), string.Join(" AND ", table.PrimaryKeys.Select(p => $"`{p}` = ?{p}")));
        }

        /// <summary>
        /// 获取通过自增主键查询的 SQL 语句。
        /// </summary>
        /// <param name="objType"></param>
        /// <param name="tableName">表名。</param>
        /// <returns></returns>
        internal static string GetSelectSqlByAutoIncrementPrimaryKey(this Type objType, string tableName = null)
        {
            var table = GetEntityColumns(objType);

            if (table.PrimaryKeys.Count == 0)
            {
                throw new InvalidOperationException("当前 Entity 没有定义主键。");
            }

            if (table.PrimaryKeys.Count > 1)
            {
                throw new InvalidOperationException("当前 Entity 存在多个主键。");
            }

            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = table.TableName;
            }

            return string.Format("SELECT `{1}` FROM `{0}` WHERE `{2}` = LAST_INSERT_ID()", tableName, string.Join("`, `", table.AllColumns), table.PrimaryKeys.First());
        }

        /// <summary>
        /// 获取插入的 SQL 语句。
        /// </summary>
        /// <param name="objType"></param>
        /// <param name="tableName">表名。</param>
        /// <returns></returns>
        internal static string GetInsertSql(this Type objType, string tableName = null)
        {
            var table = GetEntityColumns(objType);

            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = table.TableName;
            }

            return string.Format("INSERT INTO `{0}` (`{1}`) VALUES (?{2})", tableName, string.Join("`, `", table.WriteableColumns), string.Join(", ?", table.WriteableColumns));
        }

        /// <summary>
        /// 获取更新所有的 SQL 语句。
        /// </summary>
        /// <param name="objType"></param>
        /// <param name="tableName">表名。</param>
        /// <param name="excludedColumns">排除的列。</param>
        /// <returns></returns>
        internal static string GetUpdateSql(this Type objType, string tableName = null, IEnumerable<string> excludedColumns = null)
        {
            var table = GetEntityColumns(objType);

            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = table.TableName;
            }

            var updateColumns = table.WriteableColumns.AsEnumerable();

            if (excludedColumns != null)
            {
                updateColumns = updateColumns.Except(excludedColumns, StringComparer.InvariantCultureIgnoreCase);
            }

            return string.Format("UPDATE `{0}` SET {1}", tableName, string.Join(", ", updateColumns.Select(p => $"`{p}` = ?{p}")));
        }

        /// <summary>
        /// 获取通过主键更新的 SQL 语句。
        /// </summary>
        /// <param name="objType"></param>
        /// <param name="tableName">表名。</param>
        /// <param name="excludedColumns">排除的列。</param>
        /// <returns></returns>
        internal static string GetUpdateSqlByPrimaryKeys(this Type objType, string tableName = null, IEnumerable<string> excludedColumns = null)
        {
            var table = GetEntityColumns(objType);

            if (table.PrimaryKeys.Count == 0)
            {
                throw new InvalidOperationException("当前 Entity 没有定义主键。");
            }

            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = table.TableName;
            }

            var updateColumns = table.WriteableColumns.AsEnumerable();

            if (excludedColumns != null)
            {
                updateColumns = updateColumns.Except(excludedColumns, StringComparer.InvariantCultureIgnoreCase);
            }

            return string.Format("UPDATE `{0}` SET {1} WHERE {2}", tableName, string.Join(", ", updateColumns.Select(p => $"`{p}` = ?{p}")), string.Join(" AND ", table.PrimaryKeys.Select(p => $"`{p}` = ?{p}")));
        }

        private static bool IsAutoIncrement(this Type objType)
        {
            var table = GetEntityColumns(objType);

            return table.IsAutoIncrement;
        }

        private static (string TableName, List<string> AllColumns, List<string> WriteableColumns, List<string> PrimaryKeys, bool IsAutoIncrement) GetEntityColumns(Type objectType)
        {
            if (!CachedObjectProperties.ContainsKey(objectType))
            {
                var tableName = (objectType.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault() as TableAttribute)?.Name;

                if (string.IsNullOrWhiteSpace(tableName))
                {
                    if (objectType.Name.EndsWith("Entity", StringComparison.InvariantCultureIgnoreCase))
                    {
                        tableName = objectType.Name.Substring(0, objectType.Name.Length - 6);
                    }
                    else
                    {
                        tableName = objectType.Name;
                    }
                }

                List<string> allColumns = new List<string>();
                List<string> writeableColumns = new List<string>();
                List<string> primaryKeys = new List<string>();
                bool? isAutoIncrement = null;

                foreach (var item in objectType.GetProperties())
                {
                    if (item.GetCustomAttributes(typeof(NotMappedAttribute), false).FirstOrDefault() is NotMappedAttribute)
                    {
                        continue;
                    }

                    allColumns.Add(item.Name);

                    var databaseGenerated = item.GetCustomAttributes(typeof(DatabaseGeneratedAttribute), false).FirstOrDefault() as DatabaseGeneratedAttribute;
                    var key = item.GetCustomAttributes(typeof(KeyAttribute), false).FirstOrDefault() as KeyAttribute;

                    if (databaseGenerated == null || databaseGenerated.DatabaseGeneratedOption == DatabaseGeneratedOption.None)
                    {
                        writeableColumns.Add(item.Name);
                    }

                    if (key != null)
                    {
                        if (databaseGenerated?.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity)
                        {
                            if (isAutoIncrement == null)
                            {
                                isAutoIncrement = true;
                            }
                            else
                            {
                                isAutoIncrement = false;
                            }
                        }

                        primaryKeys.Add(item.Name);
                    }
                }

                CachedObjectProperties[objectType] = (tableName, allColumns, writeableColumns, primaryKeys, isAutoIncrement == true);
            }

            return CachedObjectProperties[objectType];
        }
        #endregion

        private static void CheckSqlTextOrScriptName(string sqlTextOrScriptName)
        {
            if (string.IsNullOrWhiteSpace(sqlTextOrScriptName))
            {
                throw new ArgumentNullException(nameof(sqlTextOrScriptName));
            }
        }
        public static T QueryEntity<T>(this IDbConnection conn, string sqlTextOrScriptName, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            return conn.QueryFirstOrDefault<T>(sqlTextOrScriptName, parameters, transaction, commandTimeout, commandType);
            
        }

        public static async Task<T> QueryEntityAsync<T>(this IDbConnection conn, string sqlTextOrScriptName, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            CheckSqlTextOrScriptName(sqlTextOrScriptName);

            return await conn.QueryFirstOrDefaultAsync<T>(sqlTextOrScriptName, parameters, transaction, commandTimeout, commandType);
        }

        public static (IEnumerable<T1>, T2) QueryMultiple<T1, T2>(IDbConnection cnn, CommandDefinition command)
        {
            var results = SqlMapper.QueryMultiple(cnn, command);

            if (results == null)
            {
                return (default, default);
            }

            return (results.Read<T1>(), results.ReadFirstOrDefault<T2>());
        }

        public static (IEnumerable<T1>, T2) QueryMultiple<T1, T2>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var results = SqlMapper.QueryMultiple(cnn, sql, param, transaction, commandTimeout, commandType);

            if (results == null)
            {
                return (default, default);
            }

            return (results.Read<T1>(), results.ReadFirstOrDefault<T2>());
        }

        public static async Task<(IEnumerable<T1>, T2)> QueryMultipleAsync<T1, T2>(IDbConnection cnn, CommandDefinition command)
        {
            var results = await SqlMapper.QueryMultipleAsync(cnn, command);

            if (results == null)
            {
                return (default, default);
            }

            return (results.Read<T1>(), results.ReadFirstOrDefault<T2>());
        }

        public static async Task<(IEnumerable<T1>, T2)> QueryMultipleAsync<T1, T2>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var results = await SqlMapper.QueryMultipleAsync(cnn, sql, param, transaction, commandTimeout, commandType);

            if (results == null)
            {
                return (default, default);
            }

            return (results.Read<T1>(), results.ReadFirstOrDefault<T2>());
        }

        public static async Task<int> TransactionExecuteAsync(this IDbConnection cnn, IEnumerable<string> sqls, IEnumerable<object> parametrers, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            int count = 0;
            for (int i = 0; i < sqls.Count(); i++)
            {
                count += await cnn.ExecuteAsync(sqls.ElementAt(i), parametrers.ElementAt(i), transaction, commandTimeout, commandType);
            }
            return count;
        }

        public static async Task<(IEnumerable<T>,int)> QueryPageableListAsync<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var results = await SqlMapper.QueryMultipleAsync(cnn, sql, param, transaction, commandTimeout, commandType);

            if (results == null)
            {
                return (default, default);
            }

            return (results.Read<T>(), results.ReadFirstOrDefault<int>());
        }

        public static T Insert<T>(this IDbConnection cnn, T entity, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            Type entityType = typeof(T);
            string sql = entityType.GetInsertSql() + "; ";

            if (entityType.IsAutoIncrement())
            {
                sql += entityType.GetSelectSqlByAutoIncrementPrimaryKey();
            }
            else
            {
                sql += entityType.GetSelectSqlByPrimaryKeys();
            }

            return cnn.QueryFirstOrDefault<T>(sql, entity, transaction, commandTimeout, CommandType.Text);
        }

        public static Task<T> InsertAsync<T>(this IDbConnection cnn, T entity, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            Type entityType = typeof(T);
            string sql = entityType.GetInsertSql() + "; ";

            if (entityType.IsAutoIncrement())
            {
                sql += entityType.GetSelectSqlByAutoIncrementPrimaryKey();
            }
            else
            {
                sql += entityType.GetSelectSqlByPrimaryKeys();
            }

            return cnn.QueryFirstOrDefaultAsync<T>(sql, entity, transaction, commandTimeout, CommandType.Text);
        }

        public static int InsertAll<T>(this IDbConnection cnn, IEnumerable<T> entities, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            string sql = typeof(T).GetInsertSql();

            return cnn.Execute(sql, entities, transaction, commandTimeout, CommandType.Text);
        }

        public static Task<int> InsertAllAsync<T>(this IDbConnection cnn, IEnumerable<T> entities, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            string sql = typeof(T).GetInsertSql();

            return cnn.ExecuteAsync(sql, entities, transaction, commandTimeout, CommandType.Text);
        }

        public static Task<int> InsertAllAsync<T>(this IDbConnection cnn, IEnumerable<T> entities, string tableName, IDbTransaction transaction = null
            , int? commandTimeout = null)
        {
            string sql = typeof(T).GetInsertSql(tableName);

            return cnn.ExecuteAsync(sql, entities, transaction, commandTimeout, CommandType.Text);
        }

        public static T Update<T>(this IDbConnection cnn, T entity, IDbTransaction transaction = null, int? commandTimeout = null, IEnumerable<string> excludedColumns = null)
        {
            Type entityType = typeof(T);
            string sql = entityType.GetUpdateSqlByPrimaryKeys(excludedColumns: excludedColumns) + "; " + entityType.GetSelectSqlByPrimaryKeys();

            return cnn.QueryFirstOrDefault<T>(sql, entity, transaction, commandTimeout, CommandType.Text);
        }

        public static Task<T> UpdateAsync<T>(this IDbConnection cnn, T entity, IDbTransaction transaction = null, int? commandTimeout = null, IEnumerable<string> excludedColumns = null)
        {
            Type entityType = typeof(T);
            string sql = entityType.GetUpdateSqlByPrimaryKeys(excludedColumns: excludedColumns) + "; " + entityType.GetSelectSqlByPrimaryKeys();

            return cnn.QueryFirstOrDefaultAsync<T>(sql, entity, transaction, commandTimeout, CommandType.Text);
        }

        public static int UpdateAll<T>(this IDbConnection cnn, IEnumerable<T> entities, IDbTransaction transaction = null, int? commandTimeout = null, IEnumerable<string> excludedColumns = null)
        {
            string sql = typeof(T).GetUpdateSqlByPrimaryKeys(excludedColumns: excludedColumns);

            return cnn.Execute(sql, entities, transaction, commandTimeout, CommandType.Text);
        }

        public static Task<int> UpdateAllAsync<T>(this IDbConnection cnn, IEnumerable<T> entities, IDbTransaction transaction = null, int? commandTimeout = null, IEnumerable<string> excludedColumns = null)
        {
            string sql = typeof(T).GetUpdateSqlByPrimaryKeys(excludedColumns: excludedColumns);

            return cnn.ExecuteAsync(sql, entities, transaction, commandTimeout, CommandType.Text);
        }

        public static async Task<IDictionary<TKey, TValue>> QueryDictionaryAsync<TKey, TValue>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, IEqualityComparer<TKey> comparer = null)
        {
            var results = await cnn.QueryAsync<(TKey, TValue)>(sql, param, transaction, commandTimeout: commandTimeout, commandType: commandType);

            if (comparer != null)
            {
                return results.ToDictionary(p => p.Item1, p => p.Item2, comparer);
            }
            else
            {
                return results.ToDictionary(p => p.Item1, p => p.Item2);
            }
        }

    }
}